package com.querie.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Statement;

public class BaseDAO {

	protected List getList(String sql, PreparedStatementSetter pss,
			ResultSetExtractor rse) throws Exception {
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			c = ConnectionFactory.getConnection();
			ps = c.prepareStatement(sql);
			if (pss != null) {
				pss.prepare(ps);
			}
			rs = ps.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				list.add(rse.extract(rs));
			}
			if (rs!=null)
			{
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			return list;
		} catch (Exception e) {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (c != null) {
				// c.close();
			}
			throw e;
		}
	}

	protected List getList(String sql, ResultSetExtractor rse) throws Exception {
		return getList(sql, null, rse);
	}

	protected Object getRow(String sql, PreparedStatementSetter pss,
			ResultSetExtractor rse) throws Exception {
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Object result = null;
		try {
			c = ConnectionFactory.getConnection();
			ps = c.prepareStatement(sql);
			if (pss != null) {
				pss.prepare(ps);
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				result  = rse.extract(rs);
			}
			if (rs!=null)
			{
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			return result;
		} catch (Exception e) {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (c != null) {
				// c.close();
			}
			throw e;
		}
	}

	protected int executeUpdate(String sql, PreparedStatementSetter pss)
			throws Exception {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			c = ConnectionFactory.getConnection();
			ps = c.prepareStatement(sql);
			if (pss != null) {
				pss.prepare(ps);
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			if (ps != null) {
				ps.close();
			}
			if (c != null) {
				// c.close();
			}
			throw e;
		}
	}

	protected long executeInsert(String sql, PreparedStatementSetter pss)
			throws Exception {
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		long id = -1;
		try {
			c = ConnectionFactory.getConnection();
			ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			if (pss != null) {
				pss.prepare(ps);
			}
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			if (rs != null && rs.next()) {
				id = rs.getInt(1);
			}
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			return id;
		} catch (Exception e) {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (c != null) {
				// c.close();
			}
			throw e;
		}
	}
}
